package com.example.springdatajpaderivedquerymethod.repositories;

import com.example.springdatajpaderivedquerymethod.inter.EmployeeSalary;
import com.example.springdatajpaderivedquerymethod.model.Employee;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Map;
import java.util.Optional;

@Repository
public interface EmployeeRepository extends CrudRepository<Employee,Long> {

    List<Employee> findByDept(String deptName);

    List<Employee> findBySalaryGreaterThan(int salary);

    List<Employee> findByDeptAndSalaryLessThan(String deptName,int salary);

    Employee findTopByOrderBySalaryDesc();

    Employee findTopByOrderBySalaryAsc();

    List<Employee> findTop3ByOrderBySalaryDesc();

    List<Employee> findTop3ByOrderBySalaryAsc();

    List<Employee> findFirst2BySalary(int salary);

    List<Employee> findFirst2ByDeptOrderBySalaryDesc(String deptName);

    List<Employee> findByNameLike(String name);

    long countByNameEndingWith(String endString);

    long countByNameLike(String likeString);

    List<Object[]> findMaxSalariesByDept(List<String> deptNames);

    @Query("SELECT e.dept,MIN(e.salary) FROM Employee e GROUP BY e.dept HAVING e.dept in ?1")
    List<Object[]> findMinSalariesByDept(List<String> deptNames);

    @Query("select e from Employee e where e.name like ?1")
    List<Employee> findByName(String name);

    @Query("select e from Employee e where e.name like %?1%")
    List<Employee> findByName2(String name);

    List<Employee> findByNameContaining(String name);

    @Query(value = "select max(avg_sal),min(avg_sal) from (select dept,avg(salary) as avg_sal from employee group by dept having dept in ?1) as table1", nativeQuery = true)
    List<List<Integer>> findMaxMinAvgSalariesByDept(List<String> deptNames);

    @Query("select count(e.salary) from Employee e where e.dept = :dept")
    long countByDeptNamed(@Param("dept") final String deptName);

    @Query(value = "select e from #{#entityName} e where e.dept = :department")
    List<Employee> findByDepartment(@Param("department") String department);

    List<Employee> findByDept(String deptName, Sort sort);

    Page<Employee> findByDept(String deptName, Pageable pageable);

    @Transactional
    @Modifying
    @Query(value = "update Employee e set e.salary = e.salary + e.salary * :byPercent/100 where e.dept = :dept")
    int updateDeptSalaries(@Param("dept") String dept,@Param("byPercent") Integer byPercent);

    @Transactional
    @Modifying
    int deleteByDeptAndName(String dept,String name);

    @Transactional
    @Modifying
    int deleteBySalaryGreaterThanEqual(int salary);

    List<EmployeeSalary> findBy();

    List<EmployeeSalary> findBySalaryBetween(int salaryMin,int salaryMax);

    @Query(value = "select * from Employee e where e.dept = :dept ",nativeQuery = true)
    Page<Employee> findByDeptNative(@Param("dept") String dept,Pageable pageable);
}
